using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BeeldBank.Data;

namespace BeeldBank.Data.Sql
{
	/// <summary>
	/// CollectieBase - Provides Data Access methods for Collectie.
	/// </summary>
	/// <remarks>Generated class: DO NOT MODIFY!</remarks>
	public class CollectieBase
	{
		/// <summary>
		/// Initializes a new instance of BeeldBank.Data.Base.CollectieDaoBase.
		/// </summary>
		public CollectieBase()
		{
		}
		
		#region Insert
		
		/// <summary>
		/// Insert a new record in the COLLECTION table.
		/// </summary>
		/// <param name="dataRow">DataRow containing the data to insert the COLLECTION table.</param>
		public static void Insert(DataRow dataRow)
		{
			SqlConnection sqlConnection = new SqlConnection();
			sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
			sqlConnection.Open();
			SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
			try
			{
				Insert(dataRow, sqlTransaction);
				sqlTransaction.Commit();
			}
			finally
			{
				sqlConnection.Close();
			}
		}

		/// <summary>
		/// Insert a new record in the COLLECTION table.
		/// </summary>
		/// <param name="dataRow">DataRow containing the data to insert the COLLECTION table.</param>
		/// <param name="transaction">Transaction to execute the insert in.</param>
		public static void Insert(DataRow dataRow, System.Data.IDbTransaction transaction)
		{
			SqlTransaction sqlTransaction = (SqlTransaction)transaction;
			// Setup DataAdapter
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
			sqlDataAdapter.InsertCommand = new SqlCommand();
			sqlDataAdapter.InsertCommand.Connection = sqlTransaction.Connection;
			sqlDataAdapter.InsertCommand.Transaction = sqlTransaction;
			sqlDataAdapter.InsertCommand.CommandText = "BEELDBANK_COLLECTION_INSERT";
			sqlDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure;
			
			// Add parameters
			SqlParameter sqlParameter;
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.ReturnValue;
			sqlParameter.ParameterName = "@return";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);
			
			// Id
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.Output;
			sqlParameter.ParameterName = "@id";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Name
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@name";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = dataRow["Name"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Description
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@description";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = dataRow["Description"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Keywords
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@keywords";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = dataRow["Keywords"];
            sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);

            // Version
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.Output;
			sqlParameter.ParameterName = "@version";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlDataAdapter.InsertCommand.Parameters.Add(sqlParameter);
			
			// Execute Stored procedure
			try
			{
				sqlDataAdapter.InsertCommand.ExecuteNonQuery();
			}
			catch (Exception error)
			{
				sqlTransaction.Rollback();
				if (sqlDataAdapter.InsertCommand.Parameters["@return"].Value == null)
				{
					throw new ApplicationException("Unexpected error.", error);
				}
				switch ((int)sqlDataAdapter.InsertCommand.Parameters["@return"].Value)
				{
					case 0:
						// Not an SQL error
						throw new ApplicationException("Unexpected error.", error);
					case 515:
						int posStart = error.Message.IndexOf("NULL into column '");
						int posEnd = error.Message.IndexOf("'", posStart + 18);
						throw new NullNotAllowedException("COLLECTION. " + error.Message.Substring(posStart + 18, posEnd - posStart - 18));
					case 2601:
						throw new UniqueIndexException("COLLECTION");
					case 50000:
						throw new CustomException(error.Message);
					default:
						throw new ApplicationException("Unexpected Database error (" + ((int)sqlDataAdapter.InsertCommand.Parameters["@return"].Value).ToString() + ").", error);
				}
			}
			
			dataRow["Id"] = sqlDataAdapter.InsertCommand.Parameters["@id"].Value;
			
			dataRow["version"] = sqlDataAdapter.InsertCommand.Parameters["@version"].Value;
		}

		#endregion
		
		#region Update
		
		/// <summary>
		/// Update a record in the COLLECTION table.
		/// </summary>
		/// <param name="dataRow">DataRow containing the data to insert the COLLECTION table.</param>
		public static void Update(DataRow dataRow)
		{
			SqlConnection sqlConnection = new SqlConnection();
			sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
			sqlConnection.Open();
			SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
			try
			{
				Update(dataRow, sqlTransaction);
				sqlTransaction.Commit();
			}
			finally
			{
				sqlConnection.Close();
			}
		}

		/// <summary>
		/// Update a record in the COLLECTION table.
		/// </summary>
		/// <param name="dataRow">DataRow containing the data to update.</param>
		/// <param name="transaction">Transaction to execute the insert in.</param>
		public static void Update(DataRow dataRow, System.Data.IDbTransaction transaction)
		{
			SqlTransaction sqlTransaction = (SqlTransaction)transaction;
			// Setup DataAdapter
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
			sqlDataAdapter.UpdateCommand = new SqlCommand();
			sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
			sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
			sqlDataAdapter.UpdateCommand.CommandText = "BEELDBANK_COLLECTION_UPDATE";
			sqlDataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;

			// Add parameters
			SqlParameter sqlParameter;
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.ReturnValue;
			sqlParameter.ParameterName = "@return";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);
			
			// Id
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.Input;
			sqlParameter.ParameterName = "@id";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlParameter.Value = dataRow["Id"];
			sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Name
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@name";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = dataRow["Name"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Description
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@description";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = dataRow["Description"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Keywords
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@keywords";
            sqlParameter.SqlDbType = SqlDbType.VarChar;
            sqlParameter.Value = dataRow["Keywords"];
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Version
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.InputOutput;
			sqlParameter.ParameterName = "@version";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlParameter.Value = dataRow["version"];
			sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);
			
			// Execute Stored procedure
			try
			{
				sqlDataAdapter.UpdateCommand.ExecuteNonQuery();
			}
			catch (Exception error)
			{
				sqlTransaction.Rollback();
				if (sqlDataAdapter.UpdateCommand.Parameters["@return"].Value == null)
				{
					throw new ApplicationException("Unexpected error.", error);
				}
				switch ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value)
				{
					case 0:
						// Not an SQL error
						throw new ApplicationException("Unexpected error.", error);
					case 515:
						int posStart = error.Message.IndexOf("NULL into column '");
						int posEnd = error.Message.IndexOf("'", posStart + 18);
						throw new NullNotAllowedException("COLLECTION." + error.Message.Substring(posStart + 18, posEnd - posStart - 18));
					case 2601:
						throw new UniqueIndexException("COLLECTION");
					case 50000:
						throw new CustomException(error.Message);
					default:
						throw new ApplicationException("Unexpected Database error (" + ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value).ToString() + ").", error);
				}
			}
			// Check if something was deleted.
			if ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value != 1)
			{
				sqlTransaction.Rollback();
				throw new ConcurrencyException("COLLECTION");
			}
			
			dataRow["version"] = sqlDataAdapter.UpdateCommand.Parameters["@version"].Value;
			
		}

		#endregion
		
		#region Delete
		
		/// <summary>
		/// Delete a record from the COLLECTION table.
		/// </summary>
		/// <param name="dataRow">DataRow containing the data to delete the COLLECTION table.</param>
		public static void Delete(DataRow dataRow)
		{
			SqlConnection sqlConnection = new SqlConnection();
			sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
			sqlConnection.Open();
			SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
			try
			{
				Delete(dataRow, sqlTransaction);
				sqlTransaction.Commit();
			}
			finally
			{
				sqlConnection.Close();
			}
		}

		/// <summary>
		/// Delete a record from the COLLECTION table.
		/// </summary>
		/// <param name="dataRow">DataRow containing the data to delete.</param>
		/// <param name="transaction">Transaction to execute the insert in.</param>
		public static void Delete(DataRow dataRow, System.Data.IDbTransaction transaction)
		{
			SqlTransaction sqlTransaction = (SqlTransaction)transaction;
			// Setup DataAdapter
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
			sqlDataAdapter.DeleteCommand = new SqlCommand();
			sqlDataAdapter.DeleteCommand.Connection = sqlTransaction.Connection;
			sqlDataAdapter.DeleteCommand.Transaction = sqlTransaction;
			sqlDataAdapter.DeleteCommand.CommandText = "BEELDBANK_COLLECTION_DELETE";
			sqlDataAdapter.DeleteCommand.CommandType = CommandType.StoredProcedure;

			// Add parameters
			SqlParameter sqlParameter;
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.ReturnValue;
			sqlParameter.ParameterName = "@return";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlDataAdapter.DeleteCommand.Parameters.Add(sqlParameter);
			
			// Id
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.Input;
			sqlParameter.ParameterName = "@id";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlParameter.Value = dataRow["Id"];
			sqlDataAdapter.DeleteCommand.Parameters.Add(sqlParameter);
			
			// Version
			sqlParameter = new SqlParameter();
			sqlParameter.Direction = ParameterDirection.Input;
			sqlParameter.ParameterName = "@version";
			sqlParameter.SqlDbType = SqlDbType.Int;
			sqlParameter.Value = dataRow["version"];
			sqlDataAdapter.DeleteCommand.Parameters.Add(sqlParameter);
			
			// Execute Stored procedure
			try
			{
				sqlDataAdapter.DeleteCommand.ExecuteNonQuery();
			}
			catch (Exception error)
			{
				sqlTransaction.Rollback();
				switch ((int)sqlDataAdapter.DeleteCommand.Parameters["@return"].Value)
				{
					case 0:
						// Not an SQL error
						throw new ApplicationException("Unexpected error.", error);
					case 547:
						int posStart = error.Message.IndexOf("table 'BEELDBANK_");
						int length = "table 'BEELDBANK_".Length;
						int posEnd = error.Message.IndexOf("'", posStart + length);
						throw new DeleteConstraintException(error.Message.Substring(posStart + length, posEnd - posStart - length));
					default:
						throw new ApplicationException("Unexpected Database error.", error);
				}
			}
			// Check if something was deleted.
			if ((int)sqlDataAdapter.DeleteCommand.Parameters["@return"].Value != 1)
			{
				sqlTransaction.Rollback();
				throw new ConcurrencyException("COLLECTION");
			}
		}

		#endregion
		
		#region public static void Save(DataRow dataRow)
		/// <summary>
		/// Save changes from a DataRow to the COLLECTION table.
		/// </summary>
		/// <param name="dataRow">DataRow containing the data save.</param>
		public static void Save(DataRow dataRow)
		{
			SqlConnection sqlConnection = new SqlConnection();
			sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
			sqlConnection.Open();
			SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
			try
			{
				Save(dataRow, sqlTransaction);
				sqlTransaction.Commit();
			}
			finally
			{
				sqlConnection.Close();
			}
		}

		/// <summary>
		/// Save changes from a DataRow to the COLLECTION table.
		/// </summary>
		/// <param name="dataRow">DataRow containing the data save.</param>
		/// <param name="transaction">Transaction.</param>
		public static void Save(DataRow dataRow, System.Data.IDbTransaction transaction)
		{
			SqlTransaction sqlTransaction = (SqlTransaction)transaction;
			switch (dataRow.RowState)
			{
				case DataRowState.Added:
					Insert(dataRow, sqlTransaction);
					break;
				case DataRowState.Modified:
					Update(dataRow, sqlTransaction);
					break;
				case DataRowState.Deleted:
					Delete(dataRow, sqlTransaction);
					break;
				default:
					// Do nothing: DataRowState.Detached or DataRowState.Unchanged
					break;
			}
		}
		#endregion

		#region void Save(DataTable dataTable)
		/// <summary>
		/// Save changes from a DataTable to the COLLECTION table.
		/// </summary>
		/// <param name="dataTable">DataTable containing the data save.</param>
		public static void Save(DataTable dataTable)
		{
			SqlConnection sqlConnection = new SqlConnection();
			sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
			sqlConnection.Open();
			SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
			try
			{
				Save(dataTable, sqlTransaction);
				sqlTransaction.Commit();
			}
			finally
			{
				sqlConnection.Close();
			}
		}

		/// <summary>
		/// Save changes from a DataTable to the COLLECTION table.
		/// </summary>
		/// <param name="dataTable">DataTable containing the data save.</param>
		/// <param name="transaction">Transaction.</param>
		public static void Save(DataTable dataTable, System.Data.IDbTransaction transaction)
		{
			SqlTransaction sqlTransaction = (SqlTransaction)transaction;
			DataTable insertTable = dataTable.GetChanges(DataRowState.Added);
			if (insertTable != null && insertTable.Rows.Count > 0) 
			{
				foreach (DataRow dataRow in insertTable.Rows)
				{
					Insert(dataRow, sqlTransaction);
				}
			}
			
			DataTable updateTable = dataTable.GetChanges(DataRowState.Modified);
			if (updateTable != null && updateTable.Rows.Count > 0) 
			{
				foreach (DataRow dataRow in updateTable.Rows)
				{
					Update(dataRow, sqlTransaction);
				}
			}
			
			DataTable deleteTable = dataTable.GetChanges(DataRowState.Deleted);
			if (deleteTable != null && deleteTable.Rows.Count > 0) 
			{
				foreach (DataRow dataRow in deleteTable.Rows)
				{
					Delete(dataRow, sqlTransaction);
				}
			}
			return;
		}
		#endregion

		#region public static DataTable New()
		/// <summary>
		/// Create an empty DataTable with columns for the COLLECTION table.
		/// </summary>
		public static DataTable New()
		{
			
			DataTable dataTable = new DataTable("COLLECTION");
			dataTable.Columns.Add("Id",System.Type.GetType("System.Int32"));
            dataTable.Columns.Add("Name", System.Type.GetType("System.String"));
            dataTable.Columns.Add("Description", System.Type.GetType("System.String"));
            dataTable.Columns.Add("Keywords", System.Type.GetType("System.String"));
			
			dataTable.Columns.Add("CollectieId",System.Type.GetType("System.Int32"));
			dataTable.Columns.Add("version",System.Type.GetType("System.Int32"));
			
			return dataTable;
		}
		#endregion
		
		#region protected DataTable Open(string storedProcedureName, SqlParameter sqlParameter)
		
		/// <summary>
		/// Creates a DataTable with data from the COLLECTION table.
		/// </summary>
		/// <param name="storedProcedureName">Name of the Stored Procedure that is used to retrieve the data.</param>
		/// <param name="sqlParameter">Parameter that contains the argument for the Stored Procedure.</param>
		protected static DataTable Open(string storedProcedureName, SqlParameter sqlParameter)
		{
			return Open(storedProcedureName, sqlParameter, null);
		}

		/// <summary>
		/// Creates a DataTable with data from the COLLECTION table.
		/// </summary>
		/// <param name="storedProcedureName">Name of the Stored Procedure that is used to retrieve the data.</param>
		/// <param name="sqlParameters">Parameter array that contains the argument for the Stored Procedure.</param>
		protected static DataTable OpenParamArray(string storedProcedureName, SqlParameter[] sqlParameters)
		{
			return OpenParamArray(storedProcedureName, sqlParameters, null);
		}
		
		
		/// <summary>
		/// Fills a DataTable with data from the COLLECTION table.
		/// </summary>
		/// <param name="storedProcedureName">Name of the Stored Procedure that is used to retrieve the data.</param>
		/// <param name="sqlParameter">Parameter that contains the argument for the Stored Procedure.</param>
		/// <param name="dataTable">Datatable that is filled. If null, a new DataTable is created.</param>
		protected static DataTable Open(string storedProcedureName, SqlParameter sqlParameter, DataTable dataTable)
		{
			SqlParameter[] sqlParameters;
			
			if (sqlParameter != null)
			{
				sqlParameters = new SqlParameter[1];
				sqlParameters[0] = sqlParameter;
			}
			else
			{
				sqlParameters = new SqlParameter[0];
			}

			return OpenParamArray(storedProcedureName, sqlParameters, dataTable);
		}

		
		/// <summary>
		/// Fills a DataTable with data from the COLLECTION table.
		/// </summary>
		/// <param name="storedProcedureName">Name of the Stored Procedure that is used to retrieve the data.</param>
		/// <param name="sqlParameters">Parameter array that contains the arguments for the Stored Procedure.</param>
		/// <param name="dataTable">Datatable that is filled. If null, a new DataTable is created.</param>
		protected static DataTable OpenParamArray(string storedProcedureName, SqlParameter[] sqlParameters, DataTable dataTable)
		{
			// Setup Connection
			SqlConnection sqlConnection	= new SqlConnection();
			sqlConnection.ConnectionString =  ConfigurationManager.AppSettings["connectstringSql"];
			// Setup DataAdapter
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
			sqlDataAdapter.SelectCommand = new SqlCommand();
			sqlDataAdapter.SelectCommand.Connection = sqlConnection;
			sqlDataAdapter.SelectCommand.CommandText = storedProcedureName;
			sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
			
			// Add parameters
			foreach (SqlParameter sqlParameter in sqlParameters)
			{
				sqlDataAdapter.SelectCommand.Parameters.Add(sqlParameter);
			}
			
			// Fill dataset
			if (dataTable == null)
			{
				dataTable = new DataTable("Collectie");
			}
			sqlConnection.Open();
			try
			{
				sqlDataAdapter.Fill(dataTable);
			}
			catch(SqlException)
			{
				sqlConnection.Close();
				sqlConnection.Open();
				try
				{
					sqlDataAdapter.Fill(dataTable);
				}
				catch
				{
				}
				return dataTable;
			}
			finally
			{
				sqlConnection.Close();
			}
			return dataTable;
		}
		#endregion
		
		#region Open methods
		
		/// <summary>
		/// Creates a DataTable containing all records and all fields from the COLLECTION table.
		/// </summary>
		public static DataTable Open()
		{
			return Open("BEELDBANK_COLLECTION_SELECT", null);
		}
		
		/// <summary>
		/// Fills a DataTable with all records and all fields from the COLLECTION table.
		/// </summary>
		public static DataTable Open(DataTable dataTable)
		{
			return Open("BEELDBANK_COLLECTION_SELECT", null, dataTable);
		}
		
		/// <summary>
		/// Creates a DataTable containing the record specified by the primary key from the COLLECTION table.
		/// </summary>
		/// <param name="Id">Primary key value used to retrieve the record.</param>
		public static DataTable Open(int Id)
		{	
			return Open(Id, null);
		}
		
		/// <summary>
		/// Fills a DataTable with the record specified by the primary key from the COLLECTION table.
		/// </summary>
		/// <param name="Id">Primary key value used to retrieve the record.</param>
		/// <param name="dataTable">DataTable to be filled.</param>
		public static DataTable Open(int Id, DataTable dataTable)
		{
			SqlParameter sqlParameter;
			sqlParameter = new SqlParameter("@id", SqlDbType.Int);
			sqlParameter.Value = Id;
			
			return Open("BEELDBANK_COLLECTION_SELECT_FROM_ID", sqlParameter, dataTable);
		}

		/// <summary>
		/// Creates a DataTable containing records from the BEELDBANK_COLLECTION table with Name as specified in the argument.
		/// </summary>
		/// <param name="Name">Argument that is used to retrieve the data.</param>
		public static DataTable OpenFromName(string Name)
		{	
			return OpenFromName(Name, null);
		}
		
		/// <summary>
		/// Fills a DataTable with records from the BEELDBANK_COLLECTION table with Name as specified in the argument.
		/// </summary>
		/// <param name="Name">Argument that is used to retrieve the data.</param>
		/// <param name="dataTable">DataTable to be filled.</param>
		public static DataTable OpenFromName(string Name, DataTable dataTable)
		{
			SqlParameter sqlParameter;
			sqlParameter = new SqlParameter("@name", SqlDbType.VarChar);
			sqlParameter.Value = Name;
			
			return Open("BEELDBANK_COLLECTION_SELECT_FROM_NAME", sqlParameter, dataTable);
		}

        /// <summary>
        /// Creates a DataTable containing records from the BEELDBANK_COLLECTION table with Keyword as specified in the argument.
        /// </summary>
        /// <param name="Name">Argument that is used to retrieve the data.</param>
        public static DataTable OpenFromKeyword(string Keyword)
        {
            return OpenFromKeyword(Keyword, null);
        }

        /// <summary>
        /// Fills a DataTable with records from the BEELDBANK_COLLECTION table with Keyword as specified in the argument.
        /// </summary>
        /// <param name="Name">Argument that is used to retrieve the data.</param>
        /// <param name="dataTable">DataTable to be filled.</param>
        public static DataTable OpenFromKeyword(string Keyword, DataTable dataTable)
        {
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter("@keyword", SqlDbType.VarChar);
            sqlParameter.Value = Keyword;

            return Open("BEELDBANK_COLLECTION_SELECT_FROM_KEYWORD", sqlParameter, dataTable);
        }

        #endregion

        #region Show Selected methods
        /// <summary>
        /// Creates a DataTable containing the record specified by the primary key from the MEDIAOBJECT_COLLECTION table and associated tables.
        /// </summary>
        /// <param name="MediaObjectId">Primary key value used to retrieve the record.</param>
        public static DataTable OpenSelected(int MediaObjectId)
        {
            return OpenSelected(MediaObjectId, null);
        }

        /// <summary>
        /// Fills a DataTable with the record specified by the primary key from the MEDIAOBJECT_COLLECTION table and associated tables.
        /// </summary>
        /// <param name="MediaObjectId">Primary key value used to retrieve the record.</param>
        /// <param name="dataTable">DataTable to be filled.</param>
        public static DataTable OpenSelected(int MediaObjectId, DataTable dataTable)
        {
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter("@MediaObjectId", SqlDbType.Int);
            sqlParameter.Value = MediaObjectId;

            return Open("BEELDBANK_MEDIAOBJECT_COLLECTION_SELECT_FROM_ID", sqlParameter, dataTable);
        }
        #endregion

        #region Show methods
        /// <summary>
		/// Creates a DataTable containing all records and all fields from the COLLECTION table completed with the label fields of associated tables.
		/// </summary>
		public static DataTable Show()
		{
			return Open("BEELDBANK_COLLECTION_SELECT_SHOW", null);
		}
		/// <summary>
		/// Creates a DataTable containing the record specified by the primary key from the COLLECTION> table completed with the label fields of associated tables.
		/// </summary>
		/// <param name="Id">Primary key values used to retrieve the record.</param>
		public static DataTable Show(int Id)
		{
			SqlParameter sqlParameter;
			sqlParameter = new SqlParameter("@id", SqlDbType.Int);
			sqlParameter.Value = Id;

			return Open("BEELDBANK_COLLECTION_SELECT_SHOW_FROM_ID", sqlParameter);
		}
		/// <summary>
		/// Creates a DataTable containing records from the BEELDBANK_COLLECTION table with Name as specified in the argument completed with the label fields of associated tables.
		/// </summary>
		/// <param name="Name">Argument that is used to retrieve the data.</param>
		public static DataTable ShowFromName(string Name)
		{
			SqlParameter sqlParameter;
			sqlParameter = new SqlParameter("@name", SqlDbType.VarChar);
			sqlParameter.Value = Name;

			return Open("BEELDBANK_COLLECTION_SELECT_SHOW_FROM_NAME", sqlParameter);
		}
		#endregion

        #region Show Random methods
        /// <summary>
        /// Creates a DataTable containing one random record.
        /// </summary>
        public static DataTable ShowRandom()
        {
            return Open("BEELDBANK_COLLECTION_SELECT_RANDOM", null);
        }
        #endregion

        #region AddSelected
        /// <summary>
        /// Add a mediaobject to a collection in the BEELDBANK_MEDIAOBJECT_COLLECTION table.
        /// </summary>
        /// <param name="CollectionId">CollectionId of the collection.</param>
        /// <param name="MediaObjectId">MediaObjectId of media object.</param>
        public static void AddSelected(int CollectionId, int MediaObjectId)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            sqlConnection.Open();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            try
            {
                AddSelected(CollectionId, MediaObjectId, sqlTransaction);
                sqlTransaction.Commit();
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Add a mediaobject to a collection in the BEELDBANK_MEDIAOBJECT_COLLECTION table.
        /// </summary>
        /// <param name="CollectionId">CollectionId of the collection.</param>
        /// <param name="MediaObjectId">MediaObjectId of media object.</param>
        /// <param name="transaction">Transaction to execute the update in.</param>
        public static void AddSelected(int CollectionId, int MediaObjectId, System.Data.IDbTransaction transaction)
        {
            SqlTransaction sqlTransaction = (SqlTransaction)transaction;
            // Setup DataAdapter
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.UpdateCommand = new SqlCommand();
            sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
            sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
            sqlDataAdapter.UpdateCommand.CommandText = "BEELDBANK_MEDIAOBJECT_COLLECTION_ADD";
            sqlDataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;

            // Add parameters
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.ReturnValue;
            sqlParameter.ParameterName = "@return";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // CollectionId
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@collectionid";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = CollectionId;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // MediaObjectId
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@mediaobjectid";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = MediaObjectId;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Execute Stored procedure
            try
            {
                sqlDataAdapter.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                sqlTransaction.Rollback();
                if (sqlDataAdapter.UpdateCommand.Parameters["@return"].Value == null)
                {
                    throw new ApplicationException("Unexpected error.", error);
                }
                switch ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value)
                {
                    case 0:
                        // Not an SQL error
                        throw new ApplicationException("Unexpected error.", error);
                    case 515:
                        int posStart = error.Message.IndexOf("NULL into column '");
                        int posEnd = error.Message.IndexOf("'", posStart + 18);
                        throw new NullNotAllowedException("BEELDBANK_MEDIAOBJECT_COLLECTION." + error.Message.Substring(posStart + 18, posEnd - posStart - 18));
                    case 2601:
                        throw new UniqueIndexException("BEELDBANK_MEDIAOBJECT_COLLECTION");
                    case 50000:
                        throw new CustomException(error.Message);
                    default:
                        throw new ApplicationException("Unexpected Database error (" + ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value).ToString() + ").", error);
                }
            }
        }

		#endregion

        #region DeleteSelected
        /// <summary>
        /// Delete a mediaobject from a collection in the BEELDBANK_MEDIAOBJECT_COLLECTION table.
        /// </summary>
        /// <param name="CollectionId">CollectionId of the collection.</param>
        /// <param name="MediaObjectId">MediaObjectId of media object.</param>
        public static void DeleteSelected(int CollectionId, int MediaObjectId)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            sqlConnection.Open();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            try
            {
                DeleteSelected(CollectionId, MediaObjectId, sqlTransaction);
                sqlTransaction.Commit();
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Delete a mediaobject from a collection in the BEELDBANK_MEDIAOBJECT_COLLECTION table.
        /// </summary>
        /// <param name="CollectionId">CollectionId of the collection.</param>
        /// <param name="MediaObjectId">MediaObjectId of media object.</param>
        /// <param name="transaction">Transaction to execute the update in.</param>
        public static void DeleteSelected(int CollectionId, int MediaObjectId, System.Data.IDbTransaction transaction)
        {
            SqlTransaction sqlTransaction = (SqlTransaction)transaction;
            // Setup DataAdapter
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.UpdateCommand = new SqlCommand();
            sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
            sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
            sqlDataAdapter.UpdateCommand.CommandText = "BEELDBANK_MEDIAOBJECT_COLLECTION_DELETE";
            sqlDataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;

            // Add parameters
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.ReturnValue;
            sqlParameter.ParameterName = "@return";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // CollectionId
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@collectionid";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = CollectionId;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // MediaObjectId
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@mediaobjectid";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = MediaObjectId;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Execute Stored procedure
            try
            {
                sqlDataAdapter.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                sqlTransaction.Rollback();
                if (sqlDataAdapter.UpdateCommand.Parameters["@return"].Value == null)
                {
                    throw new ApplicationException("Unexpected error.", error);
                }
                switch ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value)
                {
                    case 0:
                        // Not an SQL error
                        throw new ApplicationException("Unexpected error.", error);
                    case 515:
                        int posStart = error.Message.IndexOf("NULL into column '");
                        int posEnd = error.Message.IndexOf("'", posStart + 18);
                        throw new NullNotAllowedException("BEELDBANK_MEDIAOBJECT_COLLECTION." + error.Message.Substring(posStart + 18, posEnd - posStart - 18));
                    case 2601:
                        throw new UniqueIndexException("BEELDBANK_MEDIAOBJECT_COLLECTION");
                    case 50000:
                        throw new CustomException(error.Message);
                    default:
                        throw new ApplicationException("Unexpected Database error (" + ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value).ToString() + ").", error);
                }
            }
        }

        #endregion

        #region DeleteAll
        /// <summary>
        /// Remove a mediaobject from all collections in the BEELDBANK_MEDIAOBJECT_COLLECTION table.
        /// </summary>
        /// <param name="MediaObjectId">MediaObjectId of media object.</param>
        public static void DeleteAll(int MediaObjectId)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.AppSettings["connectstringSql"];
            sqlConnection.Open();
            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            try
            {
                DeleteAll(MediaObjectId, sqlTransaction);
                sqlTransaction.Commit();
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Remove a mediaobject from all collections in the BEELDBANK_MEDIAOBJECT_COLLECTION table.
        /// </summary>
        /// <param name="MediaObjectId">MediaObjectId of media object.</param>
        /// <param name="transaction">Transaction to execute the update in.</param>
        public static void DeleteAll(int MediaObjectId, System.Data.IDbTransaction transaction)
        {
            SqlTransaction sqlTransaction = (SqlTransaction)transaction;
            // Setup DataAdapter
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.UpdateCommand = new SqlCommand();
            sqlDataAdapter.UpdateCommand.Connection = sqlTransaction.Connection;
            sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
            sqlDataAdapter.UpdateCommand.CommandText = "BEELDBANK_MEDIAOBJECT_COLLECTION_DELETE_ALL";
            sqlDataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;

            // Add parameters
            SqlParameter sqlParameter;
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.ReturnValue;
            sqlParameter.ParameterName = "@return";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // MediaObjectId
            sqlParameter = new SqlParameter();
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.ParameterName = "@mediaobjectid";
            sqlParameter.SqlDbType = SqlDbType.Int;
            sqlParameter.Value = MediaObjectId;
            sqlDataAdapter.UpdateCommand.Parameters.Add(sqlParameter);

            // Execute Stored procedure
            try
            {
                sqlDataAdapter.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                sqlTransaction.Rollback();
                if (sqlDataAdapter.UpdateCommand.Parameters["@return"].Value == null)
                {
                    throw new ApplicationException("Unexpected error.", error);
                }
                switch ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value)
                {
                    case 0:
                        // Not an SQL error
                        throw new ApplicationException("Unexpected error.", error);
                    case 515:
                        int posStart = error.Message.IndexOf("NULL into column '");
                        int posEnd = error.Message.IndexOf("'", posStart + 18);
                        throw new NullNotAllowedException("BEELDBANK_MEDIAOBJECT_COLLECTION." + error.Message.Substring(posStart + 18, posEnd - posStart - 18));
                    case 2601:
                        throw new UniqueIndexException("BEELDBANK_MEDIAOBJECT_COLLECTION");
                    case 50000:
                        throw new CustomException(error.Message);
                    default:
                        throw new ApplicationException("Unexpected Database error (" + ((int)sqlDataAdapter.UpdateCommand.Parameters["@return"].Value).ToString() + ").", error);
                }
            }
        }
        #endregion

    }
}
